﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;

namespace Shop.Implementation.Inventory
{
    public class IInventory_V1 : Interface.Inventory.IInventory
    {
        #region 获取商品及分类信息（商城前台的产品列表：一级分类编码3位，二级分类编码3位，不得修改）
        public override Dictionary<string, object> Get()
        {
            NameValueCollection nv = System.Web.HttpContext.Current.Request.QueryString;
            string _inventoryClass = nv["inventoryClass"].GetVal_String();
            //分类编码000属于我的收藏
            if (_inventoryClass == "000") { return myFavor(); }
            string _search = nv["searchInv"].GetVal_String();
            string token = nv["token"].GetVal_String();
            int idcus;
            Customer.CustomerCommon.check_token(token, out idcus);
            using (SqlConnection cn = new SqlConnection(Common.HandleSQL.cConnstr))
            {
                try
                {
                    cn.Open();
                    SqlCommand cncmd = new SqlCommand();
                    cncmd.Connection = cn;
                    #region 1、从InventoryClass获取所有的1级分类数据，并且把000我的收藏加到最前面；
                    DataTable dt0 = GetInventoryClass1(cncmd);
                    #endregion
                    #region 2、从InventoryClass获取2级分类的数据，根据传过来的参数inventoryClass来获取，如果没有传递1步获取到的第2行数据；
                    string cInvCCode1;
                    string cInvCCode2 = "";
                    if (_inventoryClass.Length < 3) { cInvCCode1 = dt0.Rows[1]["cInvCCode"].GetVal_String(); }
                    else { cInvCCode1 = _inventoryClass.Substring(0, 3); }
                    DataTable dt1 = GetInventoryClass2(cInvCCode1, cncmd);
                    if (_inventoryClass.Length == 6) { cInvCCode2 = _inventoryClass; }
                    else { if (dt1.Rows.Count > 0) { cInvCCode2 = dt1.Rows[0]["cInvCCode"].GetVal_String(); } }
                    #endregion
                    #region 3、如果传递的分类为二级分类，则从InventorySale中提取出该分类下的所有数据到临时表；

                    string tmptable = string.Empty;
                    string tbname = string.Empty;

                    if (cInvCCode2 == dt1.Rows[0]["cInvCCode"].GetVal_String() && dt1.Rows[0]["cInvCName"].GetVal_String() == "特卖区")
                    {
                        cInvCCode2 = string.Empty;
                        for (int i = 0; i < dt1.Rows.Count; i++)
                            cInvCCode2 += "'" + dt1.Rows[i]["cInvCCode"].GetVal_String() + "',";
                        cInvCCode2 = cInvCCode2.Substring(0, cInvCCode2.Length - 1);
                        tmptable = GetInventory(cncmd, cInvCCode2, true);//特价类型，需要加入过滤
                        tbname = GetPrice_Sub(tmptable, idcus, cncmd);
                    }
                    else
                    {
                        cInvCCode2 = "'" + cInvCCode2 + "'";
                        tmptable = GetInventory(cncmd, cInvCCode2);
                        tbname = GetPrice_Sub(tmptable, idcus, cncmd);
                    }

                    #endregion
                    #region 8、返回最终数据给客户端
                    string cmdsql = GetReturnSQL(tbname, idcus);
                    cncmd.CommandText = cmdsql;
                    DataTable dtinv = new DataTable();
                    dtinv.Load(cncmd.ExecuteReader());
                    dtinv.TableName = "Inventory";
                    Dictionary<string, object> dic = Common.ReturnValue.Get("sucess", true);
                    dic.Add(dt0.TableName, dt0.ToList());
                    dic.Add(dt1.TableName, dt1.ToList());
                    dic.Add(dtinv.TableName, dtinv.ToList());
                    #endregion
                    return dic;
                }
                finally
                { cn.Close(); cn.Dispose(); }
            }

        }
        /// <summary>
        /// 获取商品的1级分类
        /// </summary>
        /// <param name="cncmd"></param>
        /// <returns></returns>
        private DataTable GetInventoryClass1(SqlCommand cncmd)
        {
            string cmdsql = @"select '' as parentID,'000' as cInvCCode,'我的收藏' as cInvCName,-1 as iOrder union
               select '' as parentID,cInvCCode,cInvCName,iOrder from InventoryClass (nolock) where ISNULL(InventoryClass.iStatus, 0)= 0 and iGrade = 1 order by iOrder";
            cncmd.CommandText = cmdsql;
            DataTable dt0 = new DataTable();
            dt0.Load(cncmd.ExecuteReader());
            dt0.TableName = "InventoryClass1";
            return dt0;
        }
        /// <summary>
        /// 获取商品的2级分类
        /// </summary>
        /// <param name="cInvCCode1"></param>
        /// <param name="cncmd"></param>
        /// <returns></returns>
        private DataTable GetInventoryClass2(string cInvCCode1, SqlCommand cncmd)
        {
            string cmdsql = "select left(cInvCCode,2) as parentID,cInvCCode,cInvCName from InventoryClass (nolock) where ISNULL(InventoryClass.iStatus,0)=0 and iGrade=2 and left(cInvCCode,3)=@cInvCCode order by iOrder ";
            cncmd.CommandText = cmdsql;
            cncmd.Parameters.Add(new SqlParameter("@cInvCCode", cInvCCode1));
            DataTable dt1 = new DataTable();
            dt1.Load(cncmd.ExecuteReader());
            cncmd.Parameters.Clear();
            dt1.TableName = "InventoryClass2";
            return dt1;
        }
        private string GetInventory(SqlCommand cncmd, string cInvCCode2, bool isSpecialSales = false)
        {

            string cmdsql = "if(object_id('tempdb..#tinv') is not null) drop table #tinv";
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            cmdsql = "create table #tinv(cInvCode nvarchar(200))";
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();

            //cmdsql = @"insert into #tinv(cInvCode)
            //select cInvCode + '-' + cInvCodeSub from InventorySale where isnull(iStatus, 0) = 0 and cInvCCode ='" + cInvCCode2 + "'";
            if (isSpecialSales)//获取特价类型，非特价则过滤
            {
                cmdsql = @"insert into #tinv(cInvCode)
            select cInvCode + '-' + cInvCodeSub from InventorySale where isnull(iStatus, 0) = 0 and  cInvName1 like '%特价%' and cInvCCode in (" + cInvCCode2 + ")";
            }
            else
            {
                cmdsql = @"insert into #tinv(cInvCode)
            select cInvCode + '-' + cInvCodeSub from InventorySale where isnull(iStatus, 0) = 0 and cInvCCode in (" + cInvCCode2 + ")";
            }
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();

            return "#tinv";
        }
        #endregion

        #region 获取商品的价格信息（商城前台的购物车刷新价格调用）
        public override Dictionary<string, object> GetPrice()
        {
            NameValueCollection nvf = System.Web.HttpContext.Current.Request.Form;
            string token = nvf["token"].GetVal_String();
            int idcus;
            #region 1、根据传入的token获取对应的客户ID；
            Customer.CustomerCommon.check_token(token, out idcus);
            #endregion
            string[] data = nvf.GetValues("data[]");
            if (data.Length == 0) { return Common.ReturnValue.Get("nodata", false); }
            using (SqlConnection cn = new SqlConnection(Common.HandleSQL.cConnstr))
            {
                try
                {
                    cn.Open();
                    SqlCommand cncmd = new SqlCommand();
                    cncmd.Connection = cn;
                    #region 2、创建临时表#tInv，把DATA[]里面的每个商品插入临时表；
                    string tbname = GetPrice_Sub(idcus, data, cncmd);
                    #endregion
                    string cmdsql = GetReturnSQL(tbname, idcus);
                    cncmd.CommandText = cmdsql;
                    DataTable dt = new DataTable();
                    dt.Load(cncmd.ExecuteReader());
                    List<Dictionary<string, object>> list = dt.ToList();
                    return Common.ReturnValue.Get("sucess", list, true);
                }
                finally
                { cn.Close(); cn.Dispose(); }
            }
        }
        private string GetPrice_Sub(int idcus, string[] cinvcodes, SqlCommand cncmd)
        {
            string cmdsql;
            cmdsql = "if(object_id('tempdb..#tinv') is not null) drop table #tinv";
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            cmdsql = "create table #tinv(cInvCode nvarchar(200))";
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            //cmdsql = "insert into #tinv(cInvCode) select @cInvCode";
            cmdsql = "insert into #tinv(cInvCode)";

            //cncmd.Parameters.Add("@cInvCode", SqlDbType.NVarChar, 200);
            for (int i = 0; i < cinvcodes.Length; i++)
            {
                if (i != cinvcodes.Length - 1)
                { cmdsql += string.Format(@" select @cInvCode{0} union ", i); }
                else
                { cmdsql += string.Format("select @cInvCode{0} ", i); }
                cncmd.Parameters.Add(new SqlParameter("@cInvCode" + i.ToString(), cinvcodes[i]));
                //cncmd.ExecuteNonQuery();
            }
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            cncmd.Parameters.Clear();
            return GetPrice_Sub("#tinv", idcus, cncmd);
        }
        #endregion

        #region 搜索商品
        public override Dictionary<string, object> Search()
        {
            NameValueCollection nv = System.Web.HttpContext.Current.Request.QueryString;
            string _inventoryClass = nv["inventoryClass"].GetVal_String();
            string _search = nv["searchInv"].GetVal_String();
            string token = nv["token"].GetVal_String();
            int idcus;
            Customer.CustomerCommon.check_token(token, out idcus);
            using (SqlConnection cn = new SqlConnection(Common.HandleSQL.cConnstr))
            {
                try
                {
                    cn.Open();
                    SqlCommand cncmd = new SqlCommand();
                    cncmd.Connection = cn;
                    #region 1、获取所有搜索条件能搜索到的信息，
                    string tmptable = getInvSearch(_search, cncmd);
                    #endregion
                    #region 2、关联价格表
                    string tbname = GetPrice_Sub(tmptable, idcus, cncmd);
                    #endregion
                    #region 3、返回最终数据给客户端
                    string cmdsql = GetReturnSQL(tbname, idcus);
                    cncmd.CommandText = cmdsql;
                    DataTable dtinv = new DataTable();
                    dtinv.Load(cncmd.ExecuteReader());
                    dtinv.TableName = "Inventory";
                    Dictionary<string, object> dic = Common.ReturnValue.Get("sucess", true);
                    dic.Add(dtinv.TableName, dtinv.ToList());
                    #endregion
                    return dic;
                }
                finally
                { cn.Close(); cn.Dispose(); }
            }
        }
        private string getInvSearch(string cSearch, SqlCommand cncmd)
        {

            string cmdsql = "if(object_id('tempdb..#tinv') is not null) drop table #tinv";
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            cmdsql = "create table #tinv(cInvCode nvarchar(200))";
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();

            cmdsql = @"insert into #tinv(cInvCode) 
            select cInvCode+ '-' + cInvCodeSub from InventorySale where isnull(iStatus, 0) = 0 and
            (cInvCode like @RefValue or cInvName like @RefValue or cInvName1 like @RefValue or cDesc like @RefValue)";
            cncmd.CommandText = cmdsql;
            cncmd.CommandText = cmdsql;
            cncmd.Parameters.Add(new SqlParameter("@RefValue", "%" + cSearch + "%"));
            cncmd.ExecuteNonQuery();
            cncmd.Parameters.Clear();
            return "#tinv";
        }
        #endregion

        #region 我的收藏
        public override Dictionary<string, object> myFavor()
        {
            NameValueCollection nv = System.Web.HttpContext.Current.Request.QueryString;
            string token = nv["token"].GetVal_String();
            int idcus;
            if (!Customer.CustomerCommon.check_token(token, out idcus))
            { return Customer.CustomerCommon.NotLogin(); }
            using (SqlConnection cn = new SqlConnection(Common.HandleSQL.cConnstr))
            {
                try
                {
                    cn.Open();
                    SqlCommand cncmd = new SqlCommand();
                    cncmd.Connection = cn;
                    #region 1、获取改客户所有收藏的信息，
                    string tmptable = getInvFavor(idcus, cncmd);
                    #endregion
                    #region 2、关联价格表
                    string tbname = GetPrice_Sub(tmptable, idcus, cncmd);
                    #endregion
                    #region 3、返回最终数据给客户端
                    string cmdsql = GetReturnSQL(tbname, idcus);
                    cncmd.CommandText = cmdsql;
                    DataTable dtinv = new DataTable();
                    dtinv.Load(cncmd.ExecuteReader());
                    dtinv.TableName = "Inventory";
                    Dictionary<string, object> dic = Common.ReturnValue.Get("sucess", true);
                    dic.Add(dtinv.TableName, dtinv.ToList());
                    #endregion
                    return dic;
                }
                finally
                { cn.Close(); cn.Dispose(); }
            }
        }

        private string getInvFavor(int idcus, SqlCommand cncmd)
        {
            string cmdsql = "if(object_id('tempdb..#tinv') is not null) drop table #tinv";
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            cmdsql = "create table #tinv(cInvCode nvarchar(200))";
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            cmdsql = string.Format(@"insert into #tinv(cInvCode)
                            select cInvCode from CustomerFavourite (nolock) where IDCus = {0}
                            and cInvCode in (select cInvCode + '-' + cInvCodeSub from InventorySale (nolock) where ISNULL(iStatus, 0) = 0)",
                idcus);
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            return "#tinv";
        }
        #endregion

        #region 公共方法

        /// <summary>
        /// -最终返回的结果语句
        /// </summary>
        /// <param name="priceTbName"></param>
        /// <param name="idcus"></param>
        /// <returns></returns>
        private string GetReturnSQL(string priceTbName, int idcus)
        {
            //  + case when cast(case when invsale.iControlSale=0 then instore.iKY else invsale.iSaleQuantity end as decimal(20,0))=0 then '<font color=red>已售罄</font>' else '' end
            string cmdsql = string.Format(@"select iv.cInvCCode as parentID,iv.cInvCode+'-'+ invsale.cInvCodeSub as cInvCode,
		invsale.cInvName,invsale.cInvName1,
		isnull(invsale.cDesc,'') + case when ISNULL(invsale.iXG,0)=0 then '' else  ' 每店限购' +CAST (invsale.iXG as nvarchar) +LEFT(invsale.cDW,1) end 
      + case when cast(case when invsale.iControlSale=0 then instore.iKY else invsale.iSaleQuantity end as decimal(20,0))=0 then '<font color=red>已售罄</font>' else '' end
        as cDesc,
		tprice.iUnitPrice,iv.cDW as SKU,invsale.iConverRate,invsale.iConverDiscount,cast(tprice.iSalePrice as decimal(20,2)) as iSalePrice , invsale.cDW as SKUSale,
		case when iConverRate<=1 then '' else '每'+iv.cDW+ CAST( round(tprice.iSalePrice*1.00/invsale.iConverRate,2) as nvarchar)+'元' end as SKUSaleMemo,
		invsale.iXG,cast(case when invsale.iControlSale=0 then instore.iKY else invsale.iSaleQuantity end as decimal(20,0)) as iKC,invsale.cImgPath,invsale.cImgPathLarg ,
                left(iv.cInvCCode, 3) as parentID1,inv1.cInvCName as cInvCNameParent,inv.cInvCName,invsale.saletimeStart,invsale.saletimeEnd,iv.iUnitCost,iv.iStatus,invsale.iOrder,
                isnull(invsale.iQuantityStart,1) as iQuantityStart,isnull(invsale.iQuantityStep,1) as iQuantityStep,case when cusfav.idcus is not null then 1 else 0 end as cusfav
                from {1} tprice inner join InventorySale invsale (nolock) on invsale.cInvCode+'-'+invsale.cInvCodeSub =tprice.cInvCode 
                inner join Inventory iv (nolock) on invsale.cInvCode=iv.cInvCode
                inner join inventoryclass inv (nolock) on invsale.cInvCCode=inv.cInvCCode
                left join InventoryClass inv1 (nolock) on LEFT(invsale.cInvCCode,3)=inv1.cInvCCode 
                inner join InventoryStore (nolock) instore on iv.cInvCode=instore.cInvCode 
                left join (select * from CustomerFavourite (nolock) where idcus={0}) cusfav on tprice.cInvCode=cusfav.cinvcode", idcus, priceTbName);
            cmdsql = cmdsql + " order by invsale.iOrder,iv.cInvCode";
            return cmdsql;
        }
        /// <summary>
        /// 获取存货价格
        /// </summary>
        /// <param name="cInvCodeTmpTable"></param>
        /// <param name="idcus"></param>
        /// <param name="cncmd"></param>
        /// <returns></returns>
        private string GetPrice_Sub(string cInvCodeTmpTable, int idcus, SqlCommand cncmd)
        {
            #region 删掉不属于销售规格inventorysale中显示有效的编码
            string cmdsql = string.Empty;
            cmdsql = string.Format(@"delete from {0} where cInvCode in 
                (select cInvCode+'-'+cInvCodeSub from inventorysale (nolock) where isnull(iStatus,0)<>0 )", cInvCodeTmpTable);
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            #endregion
            #region 创建临时表#tPrice，不用保存数据，作为价格返回的保存表；
            cmdsql = "if (object_id('tempdb..#tprice') is not null) drop table #tprice";
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            cmdsql = "create table #tprice(cInvCode nvarchar(200),iUnitPrice float,iSalePrice float)";
            cncmd.CommandText = cmdsql;
            cncmd.ExecuteNonQuery();
            #endregion
            #region 调用存储过程getInventoryPrice，生成价格表#tPrice的数据
            cncmd.CommandText = string.Format("exec  getInventoryPrice {0},'{1}','{2}'", idcus, cInvCodeTmpTable, "#tprice");
            cncmd.ExecuteNonQuery();
            return "#tprice";
            #endregion
        }
        #endregion


    }
}